#!/usr/bin/env python 
# -*- coding: utf-8 -*-
import requests
from openpyxl import Workbook

ENABLE_PROXY = True  # 是否启用代理服务器，记得你自己改成False


def get_proxies():
    """
    获取代理服务器
    :return:
    """
    return {
        'http': 'http://127.0.0.1:1081',
        'https': 'http://127.0.0.1:1081',
    } if ENABLE_PROXY else None


def write_xlsx(result_list, out):
    """
    写入csv文件
    :param result_list:
    :param out:
    :return:
    """
    headers = ['Company Name', 'Ticker', 'Fiscal Year', 'CEO compensation', 'Median employee compensation',
               'CEO pay ratio']
    result_list = [headers] + result_list  # 所有行写成list
    wb = Workbook()
    ws1 = wb.active
    for row in range(len(result_list)):
        for col in range(len(headers)):
            ws1.cell(column=col + 1, row=row + 1, value="{0}".format(result_list[row][col]))
    wb.save(filename=out)



def get_json_by_year(year: int) -> []:
    url = 'https://www.bloomberg.com/bbg-gfx/graphics-data/ceo-pay-daper/fy{}/data.json'.format(year)
    resp = requests.get(url, proxies=get_proxies(),verify=False,
                        headers={  # 加头过机器人检测
                            'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.72 Safari/537.36 Edg/90.0.818.42',
                            'accept-encoding': 'gzip, deflate, br',
                            'accept-language': 'zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7',
                            'cache-control': 'no-cache',
                            'origin': 'https://www.bloomberg.com',
                            'referer': 'https://www.bloomberg.com/'
                        }
                        )
    rows = []
    try:
        companies = resp.json()['companies']  # 按json格式读取
    except ValueError:
        print(resp.status_code)
        print(resp.content)
        return rows
    for company in companies:
        company_name = company['c']
        ticker = company['t']
        fiscal_year = year
        ceo_compensation = company['cc']
        median_employee_compensation = company['ec']
        ceo_pay_ratio = company['cpr']
        row = [company_name, ticker, fiscal_year, ceo_compensation, median_employee_compensation, ceo_pay_ratio]
        rows.append(row)
    return rows


if __name__ == '__main__':
    first_year, last_year = 2017, 2024  # 手动指定开始和结束年份
    # first_year, last_year = 2017, 2017  # 手动指定开始和结束年份

    result_list = []
    for year in range(first_year, last_year + 1, 1):  # 循环[2017， 2022)
        print('processing year: {}'.format(year))
        one_year_result = get_json_by_year(year)  # 逐年获取
        result_list.extend(one_year_result)

    print("Total: " + str(len(result_list)))
    write_xlsx(result_list, 'bloomberg_out.xlsx')
